﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.IO;
using System.Data;


namespace TravelExpertsASP
{
    public partial class CustomerList : System.Web.UI.Page
    {


        ////////////////////Access the database and get all the order list, added by QingshengZhou(Jason)//////////////////
        SqlConnection myConn = new SqlConnection("server=(local);database=TravelExperts;integrated security=SSPI");
        //SqlCommand myCommand = new SqlCommand();
        public SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
        protected void Page_Load(object sender, EventArgs e)
        {
            //myCommand.Connection = myConn;
            DataTable myView = new DataTable();
            string myStr = "SELECT Customers.CustFirstName, Products.ProdName, Bookings.BookingDate, BookingDetails.BasePrice " +
            "FROM  BookingDetails INNER JOIN " +
            "Bookings ON BookingDetails.BookingId = Bookings.BookingId INNER JOIN " +
            "Customers ON Bookings.CustomerId = Customers.CustomerId INNER JOIN " +
            "Products_Suppliers ON BookingDetails.ProductSupplierId = Products_Suppliers.ProductSupplierId INNER JOIN " +
            "Products ON Products_Suppliers.ProductId = Products.ProductId " +
            "where CustEmail = " + Session["CustEmail"].ToString();
            try
            {
                myConn.Open();
                SqlCommand myCommand = new SqlCommand(myStr,myConn);
                mySqlDataAdapter.SelectCommand=myCommand;
                mySqlDataAdapter.Fill(myView);
                myTable.DataSource = myView;
                myTable.DataBind();

                decimal result = 0;

                ////////////////get the total price of one customer///////////////

                for (int i = 0; i < myView.Rows.Count; i++)
                { 
                    result += Convert.ToDecimal(myView.Rows[i][3]) ;
                }

                lblPrice.Text = "Total Price : " + Convert.ToString(result);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                myConn.Close();
            }


        }

        //protected void SqlDataSource4_Load(object sender, EventArgs e)
        //{

        //}
    }
}